//
//  PublicMessageDataBase.m
//  AAIM
//
//  Created by 远的小布丁 on 2020/1/23.
//  Copyright © 2020 AAIM. All rights reserved.
//

#import "PublicMessageDataBase.h"
@interface PublicMessageDataBase ()
@property(nonatomic, copy) NSString *tableName;

@property(nonatomic, strong) FMDatabaseQueue *dbQueue;
@end
@implementation PublicMessageDataBase
static PublicMessageDataBase *single = nil;

+ (instancetype)sharePublicMessageDataBase {
    static dispatch_once_t onceToken;
    dispatch_once(&onceToken, ^{
        single = [[self alloc] init];
    });
    return single;
}

- (instancetype)init {
    if (self = [super init]) {
        [self initDataBase];
    }
    return self;
}

- (void)initDataBase {
    NSString *dbPath  =[CacheTool readUserDefaults:@"APPDATABASE_KEY"];
    self.dbQueue = [[FMDatabaseQueue alloc] initWithPath:dbPath];
}

- (void)creatPublicTable {
    [self.dbQueue inDatabase:^(FMDatabase *db) {
        NSString *createTableSql = [NSString stringWithFormat:@"create table if not exists %@ ('ID' INTEGER PRIMARY KEY AUTOINCREMENT, 'timestamp' INTEGER,'insertTime' INTEGER,  'messageDirection' INTEGER, 'messageType' INTEGER, 'publicUdid' TEXT, 'describe' TEXT, 'isJump' TEXT,'pic' TEXT, 'title' TEXT,'name' TEXT,'head' TEXT, 'url' TEXT,'publicID' INTEGER)", self.tableName];
        [db executeUpdate:createTableSql];
    }];
    
}

- (void)insertMessage:(ChildMenusModel *)msg {
    [self.dbQueue inDatabase:^(FMDatabase *db) {
        NSString *tableNameHead = [NSString stringWithFormat:@"insert into '%@' ", self.tableName];
        long long insertTime = [ChatTool shareChatTool].currentTimestamp;
        BOOL result = NO;
        if (msg.messageType == PublicMessagePicAndText) {
            NSString *content = [NSString stringWithFormat:@"(messageDirection, messageType, timestamp, describe, isJump, pic, title, publicUdid, insertTime,url,publicID,head,name) values (?, ?, ?, ?, ?, ?, ?, ?, ?,?,?,?,?)"];
            NSString *sql = [NSString stringWithFormat:@"%@%@", tableNameHead, content];
            result = [db executeUpdate:sql, @(msg.messageDirection), @(msg.messageType), @(msg.timestamp), msg.describe, msg.isJump, msg.pic, msg.title,msg.publicUdid,@(insertTime),msg.url,@(msg.publicID),msg.head,msg.name];
        } else if (msg.messageType == PublicMessageTypeText) {
           NSString *content = [NSString stringWithFormat:@"(messageDirection, messageType, timestamp, describe, isJump, pic, title, publicUdid, insertTime,url,publicID,head,name) values (?, ?, ?, ?, ?, ?, ?, ?, ?,?,?,?,?)"];
                      NSString *sql = [NSString stringWithFormat:@"%@%@", tableNameHead, content];
                      result = [db executeUpdate:sql, @(msg.messageDirection), @(msg.messageType), @(msg.timestamp), msg.describe, msg.isJump, msg.pic, msg.title,msg.publicUdid,@(insertTime),msg.url,@(msg.publicID),msg.head,msg.name];
        }else if (msg.messageType == PublicMessagePicText){
          NSString *content = [NSString stringWithFormat:@"(messageDirection, messageType, timestamp, describe, isJump, pic, title, publicUdid, insertTime,url,publicID,head,name) values (?, ?, ?, ?, ?, ?, ?, ?, ?,?,?,?,?)"];
                     NSString *sql = [NSString stringWithFormat:@"%@%@", tableNameHead, content];
                     result = [db executeUpdate:sql, @(msg.messageDirection), @(msg.messageType), @(msg.timestamp), msg.describe, msg.isJump, msg.pic, msg.title,msg.publicUdid,@(insertTime),msg.url,@(msg.publicID),msg.head,msg.name];
        }
    }];
}



- (void)updateMessage:(ChildMenusModel *)msg statusTo:(MessageStatus)status {
    [self.dbQueue inDatabase:^(FMDatabase *db) {
        NSString *sql = [NSString stringWithFormat:@"update %@ set messageStatus = %ld where timestamp = %ld;", self.tableName, status, (long)msg.timestamp];
        [db executeUpdate:sql];
    }];
}

- (void)deleteMessage:(ChildMenusModel *)msg {
    [self.dbQueue inDatabase:^(FMDatabase *db) {
        NSString *sql = [NSString stringWithFormat:@"delete from %@ where (publicUdid = '%@' and timestamp = %ld);", self.tableName, msg.publicUdid, (long)msg.timestamp];
        [db executeUpdate:sql];
    }];
}


- (void)clearAllMessageWithPublicUdid:(NSString *)publicUdid{
    [self.dbQueue inDatabase:^(FMDatabase *db) {
        NSString *sql = [NSString stringWithFormat:@"delete from %@ where publicUdid = '%@'", self.tableName, publicUdid];
        [db executeUpdate:sql];
    }];
}

- (void)clearAllMessageWithAllPublic {
    [self.dbQueue inDatabase:^(FMDatabase *db) {
        NSString *sql = [NSString stringWithFormat:@"delete from %@", self.tableName];
        [db executeUpdate:sql];
    }];
}

// 根据publicUdid和内容模糊搜索
- (NSArray<ChildMenusModel *> *)queryMessagesWithpublicID:(NSInteger)publicID byContent:(NSString *)content {
    NSMutableArray *messages = [NSMutableArray new];
    [self.dbQueue inDatabase:^(FMDatabase *db) {
        NSString *sql = [NSString stringWithFormat:@"select * from %@ where (publicID = %ld and content like '%%%@%%')", self.tableName, publicID, content];
        FMResultSet *result = [db executeQuery:sql];
        while ([result next]) {
            [messages addObject:[self messageWith:result]];
        }
    }];
    return messages;
}
// 清除时间戳之前的聊天记录
- (void)deleteMessageBeforeTimestamp:(long long)timestamp {
    [self.dbQueue inDatabase:^(FMDatabase *db) {
        NSString *sql = [NSString stringWithFormat:@"delete from %@ where updateTimestamp < %lld;", self.tableName, timestamp];
        [db executeUpdate:sql];
    }];
    
}

// 根据udid查询所有记录
- (NSArray<ChildMenusModel *> *)queryAllMessagesWithPublicUdid:(NSString *)publicUdid{
    long long fromTime = [ChatTool shareChatTool].currentTimestamp - 3 * 60 * 60 * 1000;
    [self deleteMessageBeforeTimestamp:fromTime];
    NSMutableArray *messages = [NSMutableArray new];
    [self.dbQueue inDatabase:^(FMDatabase *db) {
        // 发送给uid的消息  接收从uid发来的消息
        NSString *sql = [NSString stringWithFormat:@"select * from %@ where (messageDirection = 0 and publicUdid = '%@') or (messageDirection = 1 and publicUdid = '%@') order by timestamp asc limit 0,20", self.tableName, publicUdid, publicUdid];
        FMResultSet *result = [db executeQuery:sql];
        while ([result next]) {
            [messages addObject:[self messageWith:result]];
        }
    }];
    return messages;
}

//查询所有公众号
- (NSArray<ChildMenusModel *> *)querypublicMessagesWithKeyWord:(NSString *) keyWord {
    NSMutableArray *messages = [NSMutableArray new];
    [self.dbQueue inDatabase:^(FMDatabase *db) {
        NSString *sql = [NSString stringWithFormat:@"select * from %@ where  msg like '%%%@%%'", self.tableName, keyWord];
        FMResultSet *result = [db executeQuery:sql];
        while ([result next]) {
            [messages addObject:[self messageWith:result]];
        }
    }];
    return messages;
}



// 分页加载，每页20条
- (NSArray *)queryMessagesWithpublicUdid:(NSString* )publicUdid toTimestamp:(long long) toTimestamp {
//    long long fromTime = [ChatTool shareChatTool].currentTimestamp - 3 * 60 * 60 * 1000;
//    [self deleteMessageBeforeTimestamp:fromTime];
    NSMutableArray *messages = [NSMutableArray new];
    [self.dbQueue inDatabase:^(FMDatabase *db) {
        if (toTimestamp == 0) { // 加载第一页
            NSString *sql = [NSString stringWithFormat:@"select * from %@ where publicUdid = %@ order by timestamp desc, msgtype asc limit 0,20", self.tableName, publicUdid];
            FMResultSet *result = [db executeQuery:sql];
            while ([result next]) {
                [messages addObject:[self messageWith:result]];
            }
        } else {
            NSString *sql = [NSString stringWithFormat:@"select * from %@ where publicUdid = %@ and timestamp < %lld order by timestamp desc, msgtype asc limit 0,20", self.tableName, publicUdid, toTimestamp];
            FMResultSet *result = [db executeQuery:sql];
            while ([result next]) {
                [messages addObject:[self messageWith:result]];
            }
        }
    }];
    return messages;
}

// 加载最新的count条数据
- (NSArray *)queryMessagesWithpublicUdid:(NSString *)publicUdid count:(NSInteger)count {
//    long long fromTime = [ChatTool shareChatTool].currentTimestamp - 3 * 60 * 60 * 1000;
//    [self deleteMessageBeforeTimestamp:fromTime];
    NSMutableArray *messages = [NSMutableArray new];
    [self.dbQueue inDatabase:^(FMDatabase *db) {
        NSString *sql = [NSString stringWithFormat:@"select * from %@ where publicUdid = %@ order by timestamp desc, msgtype asc limit 0,%ld", self.tableName, publicUdid, (long)count];
        FMResultSet *result = [db executeQuery:sql];
        while ([result next]) {
            [messages addObject:[self messageWith:result]];
        }
        [messages reverse];
    }];
    return messages;
}

- (ChildMenusModel *)messageWith:(FMResultSet *)result {
    ChildMenusModel *msg = [ChildMenusModel new];
    msg.messageType = [result intForColumn:@"messageType"];
    msg.messageDirection = [result intForColumn:@"messageDirection"];
    msg.title = [result stringForColumn:@"title"];
    msg.publicUdid = [result stringForColumn:@"publicUdid"];
    msg.timestamp = [result longLongIntForColumn:@"timestamp"];
    msg.describe = [result stringForColumn:@"describe"];
    msg.isJump = [result stringForColumn:@"isJump"];
    msg.pic = [result stringForColumn:@"pic"];
    msg.url = [result stringForColumn:@"url"];
    msg.messageId = [result longForColumn:@"messageId"];
    if (msg.messageType == PublicMessagePicAndText) {
        return msg;
    } else if (msg.messageType == PublicMessageTypeText) {
      
        return msg;
    }
    return nil;
}

#pragma mark - getter

- (NSString *)tableName {
    UserModel * model_user= [[LoginManager shareLoginManager] getLoginCustomer];
    return  [NSString stringWithFormat:@"p_publicMessageList_%@", model_user.fuuid];
}
@end
